package com.skyMailbox.sms.dao.impl;

import com.skyMailbox.sms.dao.ISmsDao;
import com.skyMailbox.sms.to.Palabra;
import com.skyMailbox.sms.to.Sms;
import com.skyMailbox.sms.to.Usuario;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.simple.ParameterizedRowMapper;
import org.springframework.jdbc.core.simple.SimpleJdbcTemplate;
import org.springframework.stereotype.Repository;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

@Repository
public class SmsDao implements ISmsDao {

    private SimpleJdbcTemplate simpleJdbcTemplate;

    @Autowired
    public void setDataSource(final DataSource dataSource) {
        this.simpleJdbcTemplate = new SimpleJdbcTemplate(dataSource);
    }


    public Sms getNumero(String c1_telefono) {
        String sql = "select * from t1_sms_info where c1_telefono = c1_telefono";

        ParameterizedRowMapper<Sms> mapper = new ParameterizedRowMapper<Sms>() {

            public Sms mapRow(ResultSet rs, int rowNum) throws SQLException {
                Sms sms = new Sms();
                sms.setC1_indice(rs.getInt("c1_indice"));
                sms.setC1_telefono(rs.getString("c1_telefono"));
                sms.setC1_fecha_hora_recepcion(rs.getTimestamp("c1_fecha_hora_recepcion"));
                sms.setC1_texto_mensaje(rs.getString("c1_texto_mensaje"));
                sms.setC1_texto_original(rs.getString("c1_texto_original"));

                return sms;
            }
        };

        return (Sms) simpleJdbcTemplate.queryForObject(sql, mapper, new Object[]{c1_telefono});
    }


    public Usuario getUsuario(String telefono) {
        String sql = "select * from t2_usuario where C2_telefono = ?";

        ParameterizedRowMapper<Usuario> mapper = new ParameterizedRowMapper<Usuario>() {

            public Usuario mapRow(ResultSet rs, int rowNum) throws SQLException {
                Usuario usuario = new Usuario();
                usuario.setC2_indice(rs.getInt("C2_indice"));
                usuario.setC2_telefono(rs.getString("C2_telefono"));
                usuario.setC2_url(rs.getString("C2_url"));
                return usuario;
            }
        };

        Object o = null;
        try {
            o = simpleJdbcTemplate.queryForObject(sql, mapper, telefono);
        } catch (final EmptyResultDataAccessException e) {
            o = null;
        }
        return o != null ? (Usuario) o : null;
    }

    public void saveUsuario(Usuario usuario) {
        String sql = "insert into t2_usuario (C2_telefono, C2_url) values (?, ?)";
        this.simpleJdbcTemplate.update(sql, new Object[]{usuario.getC2_telefono(), usuario.getC2_url()});
    }


    public Palabra getPalabra(String palabra) {
        String sql = "select * from t3_palabra where C3_palabra = ?";

        ParameterizedRowMapper<Palabra> mapper = new ParameterizedRowMapper<Palabra>() {

            public Palabra mapRow(ResultSet rs, int rowNum) throws SQLException {
                Palabra palabra = new Palabra();
                palabra.setC3_indice(rs.getInt("c3_indice"));
                palabra.setC3_palabra(rs.getString("c3_palabra"));
                palabra.setC3_fecha_hora_alta(rs.getTimestamp("c3_fecha_hora_alta"));
                palabra.setC3_usuario_alta(rs.getString("c3_usuario_alta"));
                palabra.setC3_via_alta(rs.getString("c3_via_alta"));
                return palabra;
            }
        };

        Object o = null;
        try {
            o = simpleJdbcTemplate.queryForObject(sql, mapper, palabra);
        } catch (final EmptyResultDataAccessException e) {
            o = null;
        }
        return o != null ? (Palabra) o : null;
    }

    public void savePalabra(Palabra palabra) {
        String sql = "insert into t3_palabra (c3_palabra, c3_fecha_hora_alta, c3_usuario_alta, c3_usuario_alta) values (?, ?, ?, ?)";
        Object[] parametros = new Object[]{
                palabra.getC3_palabra(),
                palabra.getC3_fecha_hora_alta(),
                palabra.getC3_usuario_alta(),
                palabra.getC3_via_alta()};
        this.simpleJdbcTemplate.update(sql, parametros);
    }

    public void eliminarPalabra(Palabra palabra) {
        String sql = "delete from t3_palabra where c3_palabra = ?";
        this.simpleJdbcTemplate.update(sql, new Object[]{palabra.getC3_palabra()});
    }


    public List<Palabra> getTodasPalabras(Palabra Opalabra) {
        String palabra = Opalabra.getC3_palabra();
        String sql;
        if (palabra != null && !"".equalsIgnoreCase(palabra)) {
            palabra += "%";
            sql = "select * from t3_palabra where c3_palabra LIKE ?";
        } else {
            sql = "select * from t3_palabra where c3_palabra != ?";
        }
        ParameterizedRowMapper<Palabra> mapper = new ParameterizedRowMapper<Palabra>() {

            public Palabra mapRow(ResultSet rs, int rowNum) throws SQLException {
                Palabra palabra = new Palabra();
                palabra.setC3_indice(rs.getInt("c3_indice"));
                palabra.setC3_palabra(rs.getString("c3_palabra"));
                palabra.setC3_fecha_hora_alta(rs.getTimestamp("c3_fecha_hora_alta"));
                palabra.setC3_usuario_alta(rs.getString("c3_usuario_alta"));
                palabra.setC3_via_alta(rs.getString("c3_via_alta"));
                return palabra;
            }
        };

        Object o = null;
        try {
            o = simpleJdbcTemplate.query(sql, mapper, palabra);
        } catch (final EmptyResultDataAccessException e) {
            o = null;
        }
        return o != null && !((List<Palabra>) o).isEmpty() ? (List<Palabra>) o : null;
    }

    public List<Usuario> getTodosUsuario(String telefono) {
        String sql = "select C1_indice , C2_telefono, C2_url from t2_usuario";

        ParameterizedRowMapper<Usuario> mapper = new ParameterizedRowMapper<Usuario>() {

            public Usuario mapRow(ResultSet rs, int rowNum) throws SQLException {
                Usuario usuario = new Usuario();
                usuario.setC2_indice(rs.getInt("C2_indice"));
                usuario.setC2_telefono(rs.getString("C2_telefono"));
                usuario.setC2_url(rs.getString("C2_url"));
                return usuario;
            }
        };

        return simpleJdbcTemplate.query(sql, mapper);
    }

    /*public void createDissociationRawChart(ArrayList wellIds, HttpSession sess) {

        Connection connection = null;

        try {
            log_.info("fetching dissociation, begin ...");
            XYSeriesCollection xyDS = new XYSeriesCollection();

            HashMap wellIdName = (HashMap) sess.getAttribute(GlobalConstants.RESULT_HASH_WELLS_ID_NAME);


            *//**
             * JDBC Query
             *//*


            log_.debug("get Initial Context");
            Context ctx = new InitialContext();
            DataSource ds = (DataSource) ctx.lookup(UtilsConstants.DATASOURCE_JNDI);
            connection = ds.getConnection();

            log_.info("start prepared statement");
            PreparedStatement ps = connection
                    .prepareStatement(GlobalConstants.DISSOCIATION_SQL);

            *//**
             * go over each well id and add values to the dataset
             *//*
            for (Iterator idIter = wellIds.iterator(); idIter.hasNext();) {
                String stringId = (String) idIter.next();

                log_.info("stringId: " + stringId);

                ps.setInt(1, Integer.parseInt(stringId));
                ps.setString(2, GlobalConstants.DISSOCIATION_RAW_CHANNEL);
                ResultSet rs = ps.executeQuery();

                XYSeries xySeries = null;

                String wellName = (String) wellIdName.get(stringId);
                if (wellName == null || wellName.length() < 1) {
                    xySeries = new XYSeries(stringId);
                } else {
                    xySeries = new XYSeries(wellName);
                }

                boolean check = false;
                while (rs.next()) {
                    check = true;
                    xySeries.add(rs.getDouble(1), rs.getDouble(2));
                }

                // if resultset has no entries of this well -> don't show it in chart
                if (check) {
                    log_.info("series has data objects");
                    xyDS.addSeries(xySeries);
                } else {
                    log_.info("series (" + stringId + "," + wellName + ") has no data associated -> skipping from chart");
                }

                rs.close();
            }
            ps.close();

            connection.close();
            ctx.close();

            JFreeChart myLineGraph = ChartFactory.createXYLineChart("Dissociation",
                    "Temperature (C)", "Raw", xyDS, PlotOrientation.VERTICAL, true,
                    true, false);
            // set background image
            myLineGraph.setBackgroundPaint(new Color(205, 205, 205));

            //    Adding the Tooltips
            XYPlot plot = myLineGraph.getXYPlot();
            SpectrumTTGenerator gen = new SpectrumTTGenerator();
            XYLineAndShapeRenderer renderer = new XYLineAndShapeRenderer();
            renderer.setToolTipGenerator(gen);

            plot.setRenderer(renderer);

            sess.setAttribute(GlobalConstants.RESULT_CHART, myLineGraph);

            log_.info("fetched dissociation");

        }
        catch (NamingException e) {
            log_.error("NamingException during creation of Dissociation Raw Chart");
            e.printStackTrace();
        }
        catch (SQLException e) {
            log_.error("SQLException during creation of Dissociation Raw Chart");
            e.printStackTrace();
        }

    }*/


}
